/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.sun.courier.dal;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author HONGQUAN
 */
public class DataAccessHelper {
    
    Connection con = null;
    Statement stmt = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;
    
    public DataAccessHelper(){
        
    }
    
    protected void driverTest() throws Exception{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (java.lang.ClassNotFoundException e) {
            throw new Exception("JDBC Driver not found!");
        }
    }
    
    protected Connection getConnection() throws Exception{
        if(this.con == null){
            driverTest();
            String url = "jdbc:sqlserver://localhost:1433;databaseName=CourierManagement";
            try {
                this.con = DriverManager.getConnection(url, "sa","123456");
            } catch (java.sql.SQLException e) {
                System.out.println("failed");
                throw new Exception("Can not connect to DataBase Server: "+url+e.getMessage());
                
            }
        }
        return this.con;
    }
    
    protected Statement getStatement() throws Exception{
        if(this.stmt==null?true:this.stmt.isClosed()){
            this.stmt = this.getConnection().createStatement();
        }
        return this.stmt;
    }
        
    public ResultSet executeQuery(String sqlQuery) throws Exception{
        try {
            this.rs = getStatement().executeQuery(sqlQuery);
        } catch (Exception e) {
            throw new Exception("Error: "+e.getMessage());
        }
        return this.rs;
    }
    
    public PreparedStatement QueryPrepareStatement(String sqlQuery) throws Exception{
        try {
            pstm = getConnection().prepareStatement(sqlQuery);
        } catch (Exception e) {
            throw new Exception("Error: "+e.getMessage());
        }
        return this.pstm;
    }
    
    public int executeUpdate(String sqlQuery) throws Exception{
        int res = 0;
        try {
            res = getStatement().executeUpdate(sqlQuery);
        } catch (Exception e) {
            throw new Exception("Error: "+e.getMessage());
        }finally{
            this.Close();
        }
        return res;
    }
    
    public ResultSet executeQueryStoreProcedure(String storeQuery) throws Exception{
        try {
            CallableStatement cstm = getConnection().prepareCall("{call }"+storeQuery);
            rs = cstm.executeQuery();
        } catch (Exception e) {
            throw new Exception("Error: "+e.getMessage());
        }
        return rs;
    }
    
    public CallableStatement updateQueryStoreProcedure(String storeQuery) throws Exception{
        CallableStatement cstm = null;
        try {
            cstm = getConnection().prepareCall("{call }"+storeQuery);
        } catch (Exception ex) {
            throw new Exception("Error: "+ex.getMessage());
        }
        return cstm;
    }
    
    public void Close() throws SQLException{
        if(this.rs!=null&&!this.rs.isClosed()){
            this.rs.close();
            this.rs= null;
        }
        if(this.stmt!=null&&!this.stmt.isClosed()){
            this.stmt.close();
            this.stmt= null;
        }
        if(this.con!=null&&!this.con.isClosed()){
            this.con.close();
            this.con=null;
        }
    }
}
